package com.LensBlaster;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.provider.MediaStore;
import android.util.Log;

/**
 * Lensblaster database access helper class. Defines CRUD operations needed
 * for LensBlaster, and provides methods for accessing Albums and Photos in 
 * LensBlaster.
 */
public class LensBlasterDB {

	// Columns for PhotoAlbum table
	public static final String PA_KEY_ROWID = "_id";
	public static final String PA_KEY_PHOTOID = "photo_id";
	public static final String PA_KEY_ALBUMID = "album_id";

	// Table info for PhotoAlbums
	private static final String PHOTOALBUM_DATABASE_TABLE = "photoalbum";
	private static final String PHOTOALBUM_DATABASE_CREATE = "create table photoalbum (_id integer primary key autoincrement, "
			+ "photo_id integer not null, album_id integer not null);";

	// Columns for Album table
	public static final String ALBUM_KEY_ROWID = "_id";
	public static final String ALBUM_KEY_TITLE = "title";

	// Table info for Albums
	private static final String ALBUM_DATABASE_TABLE = "albums";
	private static final String ALBUM_DATABASE_CREATE = "create table albums (_id integer primary key autoincrement, "
			+ "title text not null);";
	
	//Special album IDs
	public static final long ALL_PHOTOS_ALBUM_ID = -1;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private static final int DATABASE_VERSION = 1;
	private final Context mCtx;
	private static final String DATABASE_NAME = "lensblaster";
	private static final String TAG = "LensBlasterDbAdapter";

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(PHOTOALBUM_DATABASE_CREATE);
			db.execSQL(ALBUM_DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}
	
	/**
	 * Gets all albums in lensblaster database
	 * 
	 * @return	Cursor over all albums
	 */
	public Cursor fetchAllAlbums() {
		return mDb.query(ALBUM_DATABASE_TABLE, new String[] { ALBUM_KEY_ROWID,
				ALBUM_KEY_TITLE }, null, null, null, null, null);
	}

	/**
	 * Gets all photos in MediaStore database
	 * 
	 * @return	Cursor over all photos
	 */
	public Cursor fetchAllPhotos() {
		Cursor mCursor = mCtx.getContentResolver().query(android.provider.MediaStore.Images.Media.EXTERNAL_CONTENT_URI,
				null, null, null, null);

		if (mCursor != null) {
			android.util.Log.d("lens","LensBlasterDB returning all photos, size = " + mCursor.getCount());
			mCursor.moveToFirst();
		}
		
		return mCursor;
	}
	
	/**
	 * Gets all thumbnails in MediaStore database
	 * 
	 * @return Cursor over all thumbnails in MediaStore database
	 */
	public Cursor fetchAllThumbnails(){
		Cursor mCursor = mCtx.getContentResolver().query(
				MediaStore.Images.Thumbnails.EXTERNAL_CONTENT_URI,
				null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * Gets photo whose id is rowID from MediaStore database
	 * 
	 * @param rowID	Id of photo in MediaStore database
	 * @return	Cursor positioned at photo
	 */
	public Cursor fetchPhoto(long rowID) {
		Cursor mCursor = MediaStore.Images.Media.query(
				mCtx.getContentResolver(),
				null, 
				new String[] { "_id =" + rowID }, 
				null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * Gets album whose id is rowID from lensblaster database
	 * 
	 * @param rowID	id of album in lensblaster database
	 * @return	Cursor positioned at album
	 */
	public Cursor fetchAlbum(long rowID) {
		Cursor mCursor = mDb.query(
				true, 
				ALBUM_DATABASE_TABLE, 
				new String[] { ALBUM_KEY_ROWID, ALBUM_KEY_TITLE }, 
				ALBUM_KEY_ROWID + "=" + rowID, 
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * Gets all photos in selected album
	 * 
	 * @param albumID	Id of album in lensblaster database
	 * @return	Cursor over all photos in selected album
	 */
	public Cursor fetchPhotosByAlbum(long albumID) {
		Cursor mCursor;
		//gets all photos if all photos id is input
		if (albumID==ALL_PHOTOS_ALBUM_ID)
			mCursor = 
				mCtx.getContentResolver().query(
						MediaStore.Images.Media.EXTERNAL_CONTENT_URI,
						new String[] { //We alias the MediaStore columns to our columns with "AS"
								MediaStore.Images.ImageColumns._ID + " AS " + PA_KEY_ROWID,
								MediaStore.Images.ImageColumns._ID + " AS " + PA_KEY_PHOTOID
						},
						null, null, null);
		
		//gets only photos in selected album otherwise
		else
			mCursor =
				mDb.query(true,
						PHOTOALBUM_DATABASE_TABLE,
						new String[] {
							PA_KEY_ROWID,
							PA_KEY_ALBUMID,
							PA_KEY_PHOTOID
						}, 
						PA_KEY_ALBUMID + "=" + albumID, 
						null, null, null, null, null
					);
		
		if (mCursor != null)
			mCursor.moveToFirst();
		
		return mCursor;
	}

	/**
	 * Creates new album using provided title
	 * 
	 * @param title	Title of new album
	 */
	public void addAlbum(String title) {
		if ( !validateAlbumTitle(title) )
			return;
		
		ContentValues albumContent = new ContentValues();
		albumContent.put(ALBUM_KEY_TITLE, title);
		mDb.insert(ALBUM_DATABASE_TABLE, null, albumContent);
	}

	/**
	 * Deletes album with id rowId
	 * 
	 * @param rowId	Id of album in lensblaster database
	 */
	public void deleteAlbum(long rowId) {
		mDb.delete(
				ALBUM_DATABASE_TABLE, ALBUM_KEY_ROWID + "=" + rowId, null);
		mDb.delete(
				PHOTOALBUM_DATABASE_TABLE, PA_KEY_ALBUMID + "=" + rowId, null);
	}

	/**
	 * Adds selected photo to selected album
	 * 
	 * @param photoID	Id of photo in MediaStore database to add to album
	 * @param albumID	Id of album in LensBlasterDB that photo is added to
	 */
	public void addPhotoToAlbum(long photoID, long albumID) {
		//Looks for photoID/albumID combo
		Cursor mCursor =
			mDb.query(true, 
					PHOTOALBUM_DATABASE_TABLE, 
					new String[] { 
						PA_KEY_ROWID,
						PA_KEY_ALBUMID, 
						PA_KEY_PHOTOID 
					}, 
					PA_KEY_ALBUMID + "=" + albumID + 
					" and " + PA_KEY_PHOTOID + "=" + photoID, 
					null, null, null, null, null
				);
		
		//prevents user from adding photo to album if it's already there
		if (mCursor.getCount() > 0) {
			mCursor.close();
			return;
		}
		
		mCursor.close();
		
		android.util.Log.d("lens", "adding photo ID = " + photoID + " to album = " + albumID);
		ContentValues photoAlbumContent = new ContentValues();
		photoAlbumContent.put(PA_KEY_PHOTOID, photoID);
		photoAlbumContent.put(PA_KEY_ALBUMID, albumID);
		mDb.insert(PHOTOALBUM_DATABASE_TABLE, null, photoAlbumContent);
	}

	/**
	 * Deletes selected photo from selected album
	 * 
	 * @param photoID	Id of photo in MediaStore database to be removed from album
	 * @param albumID	Id of album in LensBlasterDB that photo is removed from
	 */
	public void deletePhotoFromAlbum(long photoID, long albumID) {
		mDb.delete(
				PHOTOALBUM_DATABASE_TABLE, 
				PA_KEY_ALBUMID + "=" + albumID + " AND " + PA_KEY_PHOTOID + "=" + photoID, 
				null);
	}

	/**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
	public LensBlasterDB(Context ctx) {
		this.mCtx = ctx;
	}

	/**
     * Open the lensblaster database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
	public LensBlasterDB open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * Closes the DatabaseHelper object
	 */
	public void close() {
		mDbHelper.close();
	}
	
	/**
	 * Rename album to given title
	 * 
	 * @param rowId	Id of album to be updated
	 * @param title	New title of album to be updated
	 * @return returns true if one album is updated, false otherwise
	 */
	public boolean updateAlbum(long rowId, String title) {
		if ( !validateAlbumTitle(title) )
			return false;
		
		ContentValues args = new ContentValues();
		args.put(ALBUM_KEY_TITLE, title);

		return mDb.update(ALBUM_DATABASE_TABLE, args, ALBUM_KEY_ROWID + "=" + rowId, null) > 0;
	}
	
	
	public static Cursor miniManagedQuery(Activity a, Uri uri, String selection) {
		return a.managedQuery(uri, null, selection, null, null);
	}
	
	/**
	 * Returns id of photo, given the album photo is in and its position in the album
	 * 
	 * @param albumID	ID of album that photo is in
	 * @param position	Position in album that photo is in
	 * @param columnIndex	Index of photoID column
	 * @return Id of photo at given position in given album
	 */
	public String fetchPhotoID(long albumID, int position, String columnIndex) {
		String photoID;
		Cursor c;
		
		if (albumID == ALL_PHOTOS_ALBUM_ID)
			c = fetchAllPhotos();
		else
			c = fetchPhotosByAlbum(albumID);
		
		c.moveToPosition(position);
		int pIDcol = c.getColumnIndex(columnIndex);
		photoID = c.getString(pIDcol);
		
		c.close();
		
		return photoID;
	}
	
	/**
	 * Checks to see if album title is valid (not a duplicate and non-empty)
	 * 
	 * @param title Proposed album title
	 * @return returns false for duplicate and empty titles, true otherwise
	 */
	private boolean validateAlbumTitle(String title){
		Cursor mCursor = mDb.query(
				true, 
				ALBUM_DATABASE_TABLE, 
				new String[] { 
					ALBUM_KEY_ROWID,
					ALBUM_KEY_TITLE
				}, 
				ALBUM_KEY_TITLE + "='" + title + "'", 
				null, null, null, null, null
				);
		
		//prevents duplicate album names
		if (mCursor.getCount() > 0) {
			mCursor.close();
			return false;
		}
		
		mCursor.close();
		
		//prevents empty album names
		if (title.trim().length() == 0) {
			return false;
		}else{
			return true;
		}
	}
}
